package ph.com.gs3.formalistics.model.ldao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

import org.json.JSONArray;
import org.json.JSONException;

import ph.com.gs3.formalistics.global.utilities.Serializer;
import ph.com.gs3.formalistics.global.utilities.logging.FLLogger;
import ph.com.gs3.formalistics.model.LocalDataAccessObject;
import ph.com.gs3.formalistics.model.tables.FormsTable;
import ph.com.gs3.formalistics.model.vo.business.Company;
import ph.com.gs3.formalistics.model.vo.business.document.DocumentAction;
import ph.com.gs3.formalistics.model.vo.business.form.Form;
import ph.com.gs3.formalistics.model.vo.business.form.FormFieldData;
import ph.com.gs3.formalistics.model.vo.business.form.FormFieldData.InvalidFormFieldException;
import ph.com.gs3.formalistics.model.vo.business.form.FormViewContentData;
import ph.com.gs3.formalistics.model.vo.parsers.json.DocumentActionJSONParser;
import ph.com.gs3.formalistics.model.vo.parsers.json.FormJSONParser;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class FormsLDAO extends LocalDataAccessObject {

	private static final String GENERATED_DOCUMENTS_TABLE_ID_COLUMN_NAME = "Document_id";

	public FormsLDAO(Context context) {
		super(context);
	}

	// ==================================================================================
	// {{ Query Methods

	public Form getForm(String webId, int companyId) throws JSONException,
	        InvalidFormFieldException {

		String whereClauseRaw = "f.web_id = %s AND f.company_id = %d";
		String whereClause = String.format(Locale.ENGLISH, whereClauseRaw, webId, companyId);

		return getForm(whereClause);

	}

	public Form getForm(int id) throws JSONException, InvalidFormFieldException {

		String whereClauseRaw = "f._id = %d";
		String whereClause = String.format(Locale.ENGLISH, whereClauseRaw, id);

		return getForm(whereClause);

	}

	protected Form getForm(String whereClause) throws JSONException, InvalidFormFieldException {

		// @formatter:off
		String rawQuery = "SELECT "
					+ "f.*, c._id AS company_id, c.web_id AS company_web_id, "
					+ "c.name AS company_name, c.server FROM Forms f "
					+ "LEFT JOIN Companies c ON f.company_id = c._id "
					+ "WHERE %s";
		// @formatter:on

		String query = String.format(Locale.ENGLISH, rawQuery, whereClause);

		Cursor cursor = database.rawQuery(query, null);
		cursor.moveToFirst();

		Form form = null;

		if (!cursor.isAfterLast()) {
			form = cursorToForm(cursor);
			Company company = CompaniesLDAO.cursorToCompanyWithAliasedColumns(cursor);
			form.setCompany(company);
		}

		return form;

	}

	public List<Form> getCompanyForms(int companyId) throws JSONException,
	        InvalidFormFieldException {

		List<Form> forms = new ArrayList<>();

		// @formatter:off
		String query = "SELECT "
				+ "f.*, c._id AS company_id, c.web_id AS company_web_id, "
				+ "c.name AS company_name, c.server FROM Forms f "
				+ "LEFT JOIN Companies c ON f.company_id = c._id "
				+ "WHERE f.company_id = ?";
		// @formatter:on

		Cursor cursor = database.rawQuery(query, new String[] { Integer.toString(companyId) });

		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			Form form = cursorToForm(cursor);
			Company company = CompaniesLDAO.cursorToCompanyWithAliasedColumns(cursor);
			form.setCompany(company);

			forms.add(form);

			cursor.moveToNext();
		}

		cursor.close();

		return forms;

	}

	public long getFormsCount(int companyId) {

		String selection = FormsTable.COL_COMPANY_ID + " = ?";
		String[] selectionArgs = { Integer.toString(companyId) };

		Cursor cursor = database.query(FormsTable.NAME, new String[] { FormsTable.COL_ID },
		        selection, selectionArgs, null, null, null);

		long count = cursor.getCount();
		cursor.close();

		return count;

	}

	protected List<String> getOldFieldsIdFromDocumentsTable(Form form) {

		List<String> fields = new ArrayList<>();

		String query = "PRAGMA table_info(" + form.getGeneratedFormTableName() + ");";

		Cursor cursor = database.rawQuery(query, null);

		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			int nameColIndex = cursor.getColumnIndexOrThrow("name");	// column name
			String columnName = cursor.getString(nameColIndex);

			if (!GENERATED_DOCUMENTS_TABLE_ID_COLUMN_NAME.equals(columnName)) {
				fields.add(columnName);
			}
			cursor.moveToNext();
		}

		return fields;

	}

	// }}

	public int insertForm(Form form) throws SQLException {

		ContentValues cv = createCVFromForm(form);
		return (int) database.insertOrThrow(FormsTable.NAME, null, cv);

	}

	public void updateDocumentsLastUpdateDate(int formId, String documentsLastUpdateDate) {

		ContentValues cv = new ContentValues();

		cv.put(FormsTable.COL_DOCUMENTS_LAST_UPDATE_DATE, documentsLastUpdateDate);

		String whereClause = FormsTable.COL_ID + " = ?";
		String[] whereArgs = new String[] { Integer.toString(formId) };

		long affectedRows = database.update(FormsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows <= 0) {
			throw new SQLException("Failed to update form.");
		}

	}

	public Form updateForm(Form form) throws JSONException, InvalidFormFieldException {
		ContentValues cv = createCVFromForm(form);

		String webId = form.getWebId();
		int companyId = form.getCompany().getId();

		String whereClause = FormsTable.COL_WEB_ID + " = ? AND " + FormsTable.COL_COMPANY_ID
		        + " = ?";
		String[] whereArgs = new String[] { webId, Integer.toString(companyId) };

		long affectedRows = database.update(FormsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows > 0) {
			return getForm(webId, companyId);
		} else {
			throw new SQLException("Failed to update form.");
		}

	}

	// ==================================================================================
	// {{ Table Creation & Updating

	public void updateFormTable(Form form) {

		List<String> oldFields = getOldFieldsIdFromDocumentsTable(form);
		List<FormFieldData> newFields = getNewFields(form, oldFields);

		if (newFields.size() > 0) {
			String tableName = form.getGeneratedFormTableName();

			for (FormFieldData field : newFields) {
				String query = "ALTER TABLE " + tableName + " ADD COLUMN " + field.getId()
				        + " TEXT;";
				try {
					database.execSQL(query);
				} catch (SQLException e) {
					FLLogger.w(TAG, "Failed altering table: " + tableName + ", " + e.getMessage());
				}
			}

		}

	}

	public void createFormTable(Form form) {

		String formTableName = form.getGeneratedFormTableName();
		String query = "CREATE TABLE " + formTableName + " ("
		        + GENERATED_DOCUMENTS_TABLE_ID_COLUMN_NAME + " INTEGER PRIMARY KEY, ";
		String parameterizedQueryColumns = "";

		List<FormFieldData> formFields = form.getActiveFields();

		for (FormFieldData field : formFields) {
			parameterizedQueryColumns += field.getId() + " TEXT, ";
		}

		// Trim the trailing comma
		parameterizedQueryColumns = parameterizedQueryColumns.substring(0,
		        parameterizedQueryColumns.length() - 2);

		query += parameterizedQueryColumns + ");";

		database.execSQL(query);

	}

	/**
	 * Utility method for comparing old fields to the new fields in the newForm parameter.
	 * 
	 * @param newForm
	 * @param oldFormFieldIdList
	 * @return
	 */
	protected List<FormFieldData> getNewFields(Form newForm, List<String> oldFormFieldIdList) {
		List<FormFieldData> newFields = newForm.getActiveFields();

		for (Iterator<FormFieldData> newFieldsIterator = newFields.listIterator(); newFieldsIterator
		        .hasNext();) {
			FormFieldData newField = newFieldsIterator.next();

			for (Iterator<String> oldFieldsIterator = oldFormFieldIdList.listIterator(); oldFieldsIterator
			        .hasNext();) {
				String oldField = oldFieldsIterator.next();
				if (oldField.equals(newField.getId())) {
					newFieldsIterator.remove();
					oldFieldsIterator.remove();
					break;
				}
			}

		}

		return newFields;
	}

	// }}

	private ContentValues createCVFromForm(Form form) {

		ContentValues cv = new ContentValues();

		cv.put(FormsTable.COL_WEB_ID, form.getWebId());
		cv.put(FormsTable.COL_NAME, form.getName());

		cv.put(FormsTable.COL_WORKFLOW_ID, form.getWorkflowId());
		cv.put(FormsTable.COL_WEB_TABLE_NAME, form.getWebTableName());

		cv.put(FormsTable.COL_COMPANY_ID, form.getCompany().getId());

		// Fields
		try {
			JSONArray fieldsJSONArray = FormJSONParser.createJSONFromFormContents(form
			        .getActiveFormContents());
			cv.put(FormsTable.COL_ACTIVE_FIELDS, fieldsJSONArray.toString());
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// Field Properties
		String onCreateFieldsRequired = Serializer.serializeList(form.getOnCreateFieldsRequired());
		String onCreateFieldsEnabled = Serializer.serializeList(form.getOnCreateFieldsEnabled());
		String onCreateFieldsHidden = Serializer.serializeList(form.getOnCreateFieldsHidden());

		cv.put(FormsTable.COL_ON_CREATE_FIELDS_REQUIRED, onCreateFieldsRequired);
		cv.put(FormsTable.COL_ON_CREATE_FIELDS_ENABLED, onCreateFieldsEnabled);
		cv.put(FormsTable.COL_ON_CREATE_FIELDS_HIDDEN, onCreateFieldsHidden);

		// Actions
		String actionsString = "";
		try {
			JSONArray actionsJSONArray = FormJSONParser.createJSONArrayFromActions(form
			        .getOnCreateActions());
			actionsString = actionsJSONArray.toString();
			cv.put(FormsTable.COL_ACTIONS, actionsString);
		} catch (JSONException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// cv.put(FormsTable.COL_DOCUMENTS_LAST_UPDATE_DATE,
		// form.getDocumentsLastUpdateDate());

		return cv;

	}

	private Form cursorToForm(Cursor cursor) throws JSONException, InvalidFormFieldException {
		// @formatter:off
		// Initialize indices

		int idIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_ID);
		int webIdIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_WEB_ID);
		int nameIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_NAME);

		int fieldsIndex = cursor.getColumnIndexOrThrow(FormsTable.COL_ACTIVE_FIELDS);

		int onCreateFieldsEnabledIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_ON_CREATE_FIELDS_ENABLED);
		int onCreateFieldsRequiredIndex = cursor.getColumnIndexOrThrow(FormsTable.COL_ON_CREATE_FIELDS_REQUIRED);
		int onCreateFieldsHiddenIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_ON_CREATE_FIELDS_HIDDEN);

		int actionsIndex = cursor.getColumnIndexOrThrow(FormsTable.COL_ACTIONS);

		int workflowIdIndex 	= cursor.getColumnIndexOrThrow(FormsTable.COL_WORKFLOW_ID);
		int webTableNameIndex = cursor.getColumnIndexOrThrow(FormsTable.COL_WEB_TABLE_NAME);

		int documentsLastUpdateDateIndex = cursor.getColumnIndexOrThrow(FormsTable.COL_DOCUMENTS_LAST_UPDATE_DATE);

		// Initialize values
		int id = cursor.getInt(idIndex);
		String webId = cursor.getString(webIdIndex);
		String name = cursor.getString(nameIndex);

		String workflowId 	= cursor.getString(workflowIdIndex);
		String webTableName = cursor.getString(webTableNameIndex);
		
		String rawFields					= cursor.getString(fieldsIndex);
		String rawOnCreateFieldsEnabled 	= cursor.getString(onCreateFieldsEnabledIndex);
		String rawOnCreateFieldsRequired	= cursor.getString(onCreateFieldsRequiredIndex);
		String rawOnCreateFieldsHidden		= cursor.getString(onCreateFieldsHiddenIndex);		
				
		String rawActions	= cursor.getString(actionsIndex);
		
		String documentsLastUpdateDate		= cursor.getString(documentsLastUpdateDateIndex);
		
		// @formatter:on

		// Generate the form fields
		JSONArray fieldsJSON = new JSONArray(rawFields);
		List<FormViewContentData> formContents = FormJSONParser.createFormContentsFromJSON(fieldsJSON);

		// Generate field properties
		List<String> onCreateFieldsEnabled = Serializer.unserializeList(rawOnCreateFieldsEnabled);
		List<String> onCreateFieldsRequired = Serializer.unserializeList(rawOnCreateFieldsRequired);
		List<String> onCreateFieldsHidden = Serializer.unserializeList(rawOnCreateFieldsHidden);

		JSONArray actionsJSONArray = new JSONArray(rawActions);
		List<DocumentAction> onCreateActions = DocumentActionJSONParser
		        .createFromJSON(actionsJSONArray);

		Form form = new Form();

		form.setId(id);
		form.setWebId(webId);
		form.setName(name);

		form.setWorkflowId(workflowId);
		form.setWebTableName(webTableName);

		form.setActiveFormContents(formContents);

		form.setOnCreateFieldsEnabled(onCreateFieldsEnabled);
		form.setOnCreateFieldsRequired(onCreateFieldsRequired);
		form.setOnCreateFieldsHidden(onCreateFieldsHidden);

		form.setOnCreateActions(onCreateActions);

		form.setDocumentsLastUpdateDate(documentsLastUpdateDate);

		return form;
	}

}
